﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks; 
namespace Time.DB
{ 
    public   class DbUtil
    {
        public static string ConnectionString { get; set; }
        private DbProviderFactory providerFactory;
      
        public static t_dbLink objdbLink;
        public DbUtil()
        {
            DbProviderType tempProviderType;
            objdbLink = new t_dbLink();
            string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["strCon"].ToString();
            ConnectionString = conStr;
            tempProviderType = getDbProviderType("SqlServer");
            providerFactory = ProviderFactory.GetDbProviderFactory(tempProviderType);
            if (providerFactory == null)
            {
                throw new ArgumentException("Can't load DbProviderFactory for given value of providerType");
            }
        }

        public DbUtil(string bsType)
        {
            objdbLink = new t_dbLink();
            DbProviderType tempProviderType;
            objdbLink = new t_dbLink();
            string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["strCon"].ToString();
            ConnectionString = conStr;
            tempProviderType = getDbProviderType("SqlServer");
            providerFactory = ProviderFactory.GetDbProviderFactory(tempProviderType);
            if (providerFactory == null)
            {
                throw new ArgumentException("Can't load DbProviderFactory for given value of providerType");
            }

            string sqlStr = string.Format("select * from t_Base_dbLink where bsType  = '{0}'", bsType);
            DbDataReader myReader = (SqlDataReader)ExecuteReader(sqlStr, null);
            if (myReader.Read())
            {
                objdbLink.BSType = myReader["BSType"].ToString();
                objdbLink.DBType = myReader["DBType"].ToString();
                objdbLink.DBPwd = myReader["DBPwd"].ToString();
                objdbLink.DBUser = myReader["DBUser"].ToString();
                objdbLink.DBName = myReader["DBName"].ToString();
                objdbLink.DBAddress = myReader["DBAddress"].ToString();
                 if(string.IsNullOrEmpty(objdbLink.DBAddress))
                {
                    return;
                }
                ConnectionString = DBConnectHelp.getConStr(objdbLink);
                tempProviderType = getDbProviderType(objdbLink.DBType);
                providerFactory = ProviderFactory.GetDbProviderFactory(tempProviderType);
                if (providerFactory == null)
                {
                    throw new ArgumentException("Can't load DbProviderFactory for given value of providerType");
                }
            }
        }
        
        public DbProviderType getDbProviderType(string dbType)
        {
            dbType = dbType.ToUpper() == "ACCESS" ? "OleDb" : dbType;
            DbProviderType objDbProviderType = new DbProviderType();
            foreach (string s in Enum.GetNames(typeof(DbProviderType)))
            {   
                if (s ==  dbType)
                {
                    objDbProviderType = (DbProviderType)Enum.Parse(typeof(DbProviderType), s);
                    break ;
                }
            }
            return objDbProviderType;
        }
        
        public int ExecuteNonQuery(string strSql, IList<DbParameter> parameters)
        {
            CommandType commandType;
            if (strSql.ToUpper().Contains("DELETE") == true || strSql.ToUpper().Contains("SELECT") == true || strSql.ToUpper().Contains("UPDATE") == true || strSql.ToUpper().Contains("INSERT") == true)
            {
                commandType = CommandType.Text;
            }
            else
            {
                commandType =  CommandType.StoredProcedure ;
            }
            return ExecuteNonQuery(strSql, parameters, commandType);
        }
         
        public int ExecuteNonQuery(string strSql, IList<DbParameter> parameters, CommandType commandType)
        {
            using (DbCommand command = CreateDbCommand(strSql, parameters, commandType))
            {
                command.Connection.Open();
                int affectedRows = command.ExecuteNonQuery();
                command.Connection.Close();
                return affectedRows;
            }
        }
        
         
       
        public int ExecuteNonQueryTransaction(List<string> strSQLs)
        {
            using (DbCommand myCmd = CreateDbCommand("", null, CommandType.Text))
            {
                int sumAffected = 0;
                DbTransaction transaction = myCmd.Connection.BeginTransaction();
                myCmd.Transaction = transaction;
                try
                {
                    foreach (var n in strSQLs)
                    {
                        myCmd.CommandText = n;
                        sumAffected += myCmd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    return sumAffected;
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
        
        public DbDataReader ExecuteReader(string strSql, IList<DbParameter> parameters)
        {
            CommandType commandType;
            if (strSql.ToUpper().Contains("DELETE") == true || strSql.ToUpper().Contains("SELECT") == true || strSql.ToUpper().Contains("UPDATE") == true || strSql.ToUpper().Contains("INSERT") == true)
            {
                commandType = CommandType.Text;
            }
            else
            {
                commandType = CommandType.StoredProcedure;
            }
            return ExecuteReader(strSql, parameters, commandType);
        }
       
        public DbDataReader ExecuteReader(string sql, IList<DbParameter> parameters, CommandType commandType)
        { 
            DbCommand command = CreateDbCommand(sql, parameters, commandType);
            command.Connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
       
        public DataTable ExecuteDataTable(string strSql, IList<DbParameter> parameters)
        {
            CommandType commandType;
            if (strSql.ToUpper().Contains("DELETE") == true || strSql.ToUpper().Contains("SELECT") == true || strSql.ToUpper().Contains("UPDATE") == true || strSql.ToUpper().Contains("INSERT") == true)
            {
                commandType = CommandType.Text;
            }
            else
            {
                commandType = CommandType.StoredProcedure;
            }
            return ExecuteDataTable(strSql, parameters, commandType);
        }
        
        public DataTable ExecuteDataTable(string sql, IList<DbParameter> parameters, CommandType commandType)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                using (DbDataAdapter adapter = providerFactory.CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }

       
        public Object ExecuteScalar(string strSql, IList<DbParameter> parameters)
        {
            CommandType commandType;
            if (strSql.ToUpper().Contains("DELETE") == true || strSql.ToUpper().Contains("SELECT") == true || strSql.ToUpper().Contains("UPDATE") == true || strSql.ToUpper().Contains("INSERT") == true)
            {
                commandType = CommandType.Text;
            }
            else
            {
                commandType = CommandType.StoredProcedure;
            }
            return ExecuteScalar(strSql, parameters, commandType);
        }
 
        public Object ExecuteScalar(string sql, IList<DbParameter> parameters, CommandType commandType)
        {
            using (DbCommand command = CreateDbCommand(sql, parameters, commandType))
            {
                command.Connection.Open();
                object result = command.ExecuteScalar();
                command.Connection.Close();
                return result;
            }
        }
        
        private DbCommand CreateDbCommand(string sql, IList<DbParameter> parameters, CommandType commandType)
        {
            DbConnection connection = providerFactory.CreateConnection();
            DbCommand command = providerFactory.CreateCommand();
            connection.ConnectionString = ConnectionString;
            command.CommandText = sql;
            command.CommandType = commandType;
            command.Connection = connection;
            if (!(parameters == null || parameters.Count == 0))
            {
                foreach (DbParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }
    }
    
    public class ProviderFactory
    {
        private static Dictionary<DbProviderType, string> providerInvariantNames = new Dictionary<DbProviderType, string>();
        private static Dictionary<DbProviderType, DbProviderFactory> providerFactoies = new Dictionary<DbProviderType, DbProviderFactory>(20);
        static ProviderFactory()
        {
            //加载已知的数据库访问类的程序集
            providerInvariantNames.Add(DbProviderType.SqlServer, "System.Data.SqlClient");
            providerInvariantNames.Add(DbProviderType.OleDb, "System.Data.OleDb");
            providerInvariantNames.Add(DbProviderType.ODBC, "System.Data.ODBC");
            providerInvariantNames.Add(DbProviderType.Oracle, "Oracle.DataAccess.Client");
            providerInvariantNames.Add(DbProviderType.MySql, "MySql.Data.MySqlClient");
            providerInvariantNames.Add(DbProviderType.SQLite, "System.Data.SQLite");
            providerInvariantNames.Add(DbProviderType.Firebird, "FirebirdSql.Data.Firebird");
            providerInvariantNames.Add(DbProviderType.PostgreSql, "Npgsql");
            providerInvariantNames.Add(DbProviderType.DB2, "IBM.Data.DB2.iSeries");
            providerInvariantNames.Add(DbProviderType.Informix, "IBM.Data.Informix");
            providerInvariantNames.Add(DbProviderType.SqlServerCe, "System.Data.SqlServerCe");
        }
       
        public static string GetProviderInvariantName(DbProviderType providerType)
        {
            return providerInvariantNames[providerType];
        }
        
        public static DbProviderFactory GetDbProviderFactory(DbProviderType providerType)
        {
            //如果还没有加载，则加载该DbProviderFactory
            if (!providerFactoies.ContainsKey(providerType))
            {
                providerFactoies.Add(providerType, ImportDbProviderFactory(providerType));
            }
            return providerFactoies[providerType];
        }
       
        private static DbProviderFactory ImportDbProviderFactory(DbProviderType providerType)
        {
            string providerName = providerInvariantNames[providerType];
            DbProviderFactory factory = null;
            try
            {
                //从全局程序集中查找
                factory = DbProviderFactories.GetFactory(providerName);
            }
            catch (ArgumentException e)
            {
                factory = null;
            }
            return factory;
        }
        
    } 
}